<?php if (!defined("_ECRIRE_INC_VERSION")) return; if (!class_exists('pdk_Model')) {
/**
 * SPIP Plugin Development Kit
 *
 * @author     Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
 * @license    GNU/GPL
 * @package    plugins
 * @subpackage pdk
 * @category   outils
 * @copyright  (c) Aleksey Yaroshchuk 2011-2012
 * @link       http://www.spip-contrib.net/Developmet-Kit
 * @version    2.0.0
 */

//TODO: Add logging

// Common includes:
include_spip('base/abstract_sql');
include_once(_DIR_PLUGIN_PDK.'classes/pdk_validation.php');

/**
 *  pdk Model Class
 * 
 * 	Create Read Update Delete (CRUD) base model class
 *  This is main model. All other classes may inherit it, and use all commnon CRUD methods 
 *
 * @package		plugins
 * @subpackage	pdk
 * @category	outils
 * @author		Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
 * @link		http://www.spip-contrib.net/Developmet-Kit
 */
class pdk_Model {
    /**
     *  Main table, to which all queries will be done.
     *  Note: This variable must be defined before call any method of this class
     *  Example: $main_table = 'products'  
     */
    public $main_table;
    
    /**
     *  Primary key of main table
     *  Note: This variable must be defined before call any method of this class
     *  Example: $primary_key = 'id_product'
     */
    public $primary_key;
    
    /**
     *  Array of main table fields 
     *  Note: This variable must be defined before call any method of this class
     * 
     *  Format: field_name=>field_alias. Example: $main_fields['name'] = 'name'; $main_fields['num'] = 'number'; 
     */
    public $main_fields;
    
    /**
     *  Array of joins information
     *  
     *  Format: table_name=>array('main_id'=>main_field_join_id
     *                            'join_id'=>join_table_id,
     *                            'join_type'=>join_type, // (default: 'LEFT') Put here, for example, 'INNER' or 'LEFT'.
     *                            'hard_join'=>boolean_value, // (default: TRUE) If FALSE, join table appears only in select function. If TRUE, join table and main table always come togather.
     *                            'fields'=>array(field_name=>field_alias)       
     *                            ) 
     *  Example: $joins['products_descriptions'] = array('main_id'=>'id_product',
     *                                                   'join_id'=>'id_product',
     *                                                   'fields'=>array('short_desc'=>'short_description',
     *                                                                   'full_desc'=>'full_description')                                                    
     *                                                 )   
     */
    public $joins = array();
    
    /**
     *  Last query. Now works only for select
     */
    public $last_query = '';
    
    /**
     *  pdk_Validation Object for this model. 
     *  Note: You can get access to it directly through Model class. 
     *        Example: $product_model->validation->set_rules('product_name', 'Product Name', 'required'); 
     */
    public $validation;
    
    /**
     *  Test data
     */
    public $test_data;
    
    /**
	 * Constructor
	 */
	public function __construct()
	{
	   $this->validation = new pdk_Validation();  
	}
    
    
    /**
     *  This function filters data array, checks aliases and real names,  
     *  and forms array of only needed fields data. Keys names depend on aliases keys value
     *  
     *  First this function looks at the aliases, and after that on the real names  
     * 
     *  @param array $data Assosiative array with data
     *  @param bool $only_hard_join_data If TRUE this function will return only main and hard_join tables data. Otherwise function will return all data, including non hard_join tables
     * 
     *  @return array Assosiative array of objects data  
     */
     //TODO: $only_hard_join_data переделать на $left_join_data
     //TODO: сделать так, чтобы если поле равно "on" оно автоматически заменялось на 1. Это должно конфигурироваться каким то конфигом (на уровне фугкции или класса)
    function form_data_from_array($data, $only_hard_join_data=true) {
        $row_data = array();
        
        // Main table fields:
        foreach ($this->main_fields as $real_name=>$alias) {            
            if (isset($data[$alias]))                
                $row_data[$alias] = $data[$alias];
            else if (isset($data[$real_name])) 
                $row_data[$alias] = $data[$real_name];    
        }
        
        // Join fields:
        foreach ($this->joins as $join) {
            if ($only_hard_join_data && isset($join['hard_join']) && $join['hard_join']!=TRUE) continue;
            if (isset($join['fields']) and !empty($join['fields']))
            foreach ($join['fields'] as $real_name=>$alias) {                
                if (isset($data[$alias]))                
                    $row_data[$alias] = $data[$alias];
                else if (isset($data[$real_name])) 
                    $row_data[$alias] = $data[$real_name];    
            }
        }
        
        return $row_data;
    }
    
    
    /**
	 *  Insert row of data
     *  Note: This function searches needed fields in $data array only by aliases. 
     *        So if some of your aliases are different from real names, before calling this function 
     *        use form_data_from_array() function to form correct data array 
	 *
	 *	@param array $data Array of row data
     *         !!! keys must be fields aliases!!!
     *         Example: $data=array('field_alias1'=>$value1, 'field_alias2'=>'$value2'); 
     *  @param bool $clear_id If true, field $data[$this->primary_key] will be unset
     * 	 
	 *	@return int Inserted row ID, or false if error occured	 
	 */	 	 	
	function insert($data, $clear_id = false) {
	   
       // Delete primary key field if it is needed
       if ($clear_id) {
            unset($data[$this->primary_key]); 
       }
       
	   $couples = array();
       $insert_flag = true;
       // Main fields
       $fields = array_flip($this->main_fields); 
       foreach ($data as $alias=>$value) {
           if (isset($fields[$alias]))
               $couples[$fields[$alias]] = $value;               
       }
       $inserted_id = sql_insertq($this->main_table, $couples);
       $data[$this->primary_key] = $inserted_id; 
       //TODO: Правильно хендлить ошибки при вставке, и частичные вставки
       
       // Join fields:
       if ($inserted_id) {
           foreach ($this->joins as $table_name=>$join) {
               if (isset($join['hard_join']) && $join['hard_join']!=TRUE) continue; 
               $couples = array();
               $couples[$join['join_id']] = $data[$join['main_id']];  
               if (isset($join['fields']) and !empty($join['fields'])) {
                   $fields = array_flip($join['fields']);
                   foreach ($data as $alias=>$value) {
                      if (isset($fields[$alias]))
                      $couples[$fields[$alias]] = $value;               
                   }
               }
               if (!sql_insertq($table_name, $couples)) $insert_flag = false;     
           } 
       } else $insert_flag = false;    
       
       if ($insert_flag)
            return $inserted_id;
       else  
            return FALSE;
	}
    
    /**
	 *  Get the total number of rows in database
     * 
     *  @param Array (or string) $where Where conditions. Could be array of conditions, or simple string condition. Example: "prix>100"
     *   
     *  @return int Number of rows in database
	 */
    function get_total($where = array()) {        
        return (count($this->select($where)));
        //return sql_countsel($this->main_table, $where);
    }
    
    /**
     *  Select all data rows from database
     *  Note: This function selects all fields with their aliases
     *  !!! $where conditions must contain real field names!!!
     *  
     *  @param Array (or string) $where Where conditions. Could be array of conditions, or simple string condition. Example: "prix>100"   
     *  @param Array (or string) $group_by Group by conditions. Could be array of conditions, or simple string condition. Example: "prix"
     *  @param Array (or string) $order_by Order by conditions. Could be array of conditions, or simple string condition. Example: "prix desc" 
     *  @param String $limit Limit string. Example: "5" (Limit = 5). If you want to add offset, it to this string. Example: "0, 5" (Offset = 0, Limit = 5)      
     * 
     *  @return Array Associative array of data rows appropriate for $where conditions
     */ 
    function select($where = array(), $group_by = array(), $order_by = array(), $limit = '') {
        $select = array();
        $from = array();
        
        if ($where and !is_array($where)) $where = array($where);
        else if (!$where) $where = array();
        
        // Main fields:        
        foreach ($this->main_fields as $real_name=>$alias) {
            //if ($alias != $real_name)
                $select[] = $this->main_table.'.'.$real_name.' AS '.$alias;            
        }
        $from = $this->main_table;
          
        foreach ($this->joins as $table_name=>$join) {
            $join_type = $join['join_type']?$join['join_type']:'LEFT'; // Default LEFT join
            $from .= ' '.$join_type.' JOIN '.$table_name.' ON '.
                     $this->main_table.'.'.$join['main_id'].' = '.$table_name.'.'.$join['join_id'];
            
            if (isset($join['fields']) and !empty($join['fields']))
            foreach ($join['fields'] as $real_name=>$alias) {                
                    $select[] = $table_name.'.'.$real_name.' AS '.$alias;
            }
        }
		
        $this->last_query = sql_get_select($select, $from, $where, $group_by, $order_by, $limit);
        $res = sql_select($select, $from, $where, $group_by, $order_by, $limit);
         
        return sql_fetch_all($res);
    }
    
    /**
	 *  Get row from database by ID (primary key)
     * 
     *  @param int $primary_key Primary key of row to select
     *  @param bool $empty_if_no_result If true and there is no result for this $primary_key, empty row will be returned  
	 *
     *  @return Array data row appropriate for $primary_key. 
     */
    function select_by_id($primary_key, $empty_if_no_result=false) {
        if (isset($primary_key) && $primary_key)
            $result = $this->select($this->main_table.'.'.$this->primary_key.' = '.intval($primary_key));
                       
        if (isset($result[0])) 
            return $result[0];
        else if ($empty_if_no_result)
            return $this->get_empty();
        else
            return array();        
    }
    
    /**
     *  Update data rows
     *  Note: This function searches needed fields in $data array only by aliases. 
     *        So if some of your aliases are different from real names, before calling this function 
     *        use form_data_from_array() function to form correct data array
     * 
     *  @param Array (or string) $where Where conditions. Could be array of conditions, or simple string condition. Example: "prix>100" 
     *  @param array $data Array of rows data
     *         !!! keys must be fields aliases!!!
     *         Example: $data=array('field_alias1'=>$value1, 'field_alias2'=>'$value2');
     * 	 
	 *	@return bool True if OK and False otherwise
     */
    function update($where = array(), $data = array()) {
       if ($where and !is_array($where)) $where = array($where);
       /*else*/ if (!$where) $where = array();
       
       $table = $this->main_table;
       $couples = array();
       // Main fields
       $fields = array_flip($this->main_fields); 
       foreach ($data as $alias=>$value) {
           if (isset($fields[$alias]))
               $couples[$this->main_table.'.'.$fields[$alias]] = $value;                              
       }
       // Join fields:
       foreach ($this->joins as $table_name=>$join) {
           if (isset($join['hard_join']) && $join['hard_join']!=TRUE) continue;
           $join_type = $join['join_type']?$join['join_type']:'LEFT'; // Default LEFT join
           $table .= ' '.$join_type.' JOIN '.$table_name.' ON '.
                     $this->main_table.'.'.$join['main_id'].' = '.$table_name.'.'.$join['join_id'];
           if (isset($join['fields']) and !empty($join['fields'])) {
               $fields = array_flip($join['fields']);
               foreach ($data as $alias=>$value) {
                   if (isset($fields[$alias]))
                   $couples[$table_name.'.'.$fields[$alias]] = $value;               
               }
           }        
       }
       // SET part of query
       $set_query = ' SET ';
       $i = 1;
       foreach($couples as $field=>$value) {
           $set_query .= $field.'=\''.mysql_real_escape_string($value).'\'';
           if ($i!=count($couples))
               $set_query .= ',';
           $i++;     
       }
       // WHERE part of query
       if ($where) // if we haven't got $where, we will update all rows in table
           $where_query = ' WHERE ';
       $i = 1;
       foreach($where as $w) {
           $where_query .= $w;
           if ($i!=count($where))
               $where_query .= ' AND ';
           $i++;     
       }
       // Если прописываем через join-ы, то SPIP-овская функция sql_updateq почему то не добавляет SET блок
       // Поэтому просто формируем запрос, и выполняем его
       // TODO:Нужно еще как то экранировать WHERE часть запроса
       return sql_query('UPDATE '.$table.$set_query.$where_query);
       //return sql_updateq($table, $couples, $where);
    }
    
    /**
	 *  Update row by ID (primary key)
     * 
     *  @param int $primary_key Primary key of row to update
     *  @param array $data Array of row data
     *  @param bool $insert If true and row with passed $primary_key does not exist, $data will be inserted with that $primary_key 
	 *
     *  @return bool True if OK and False otherwise
     */
    function update_by_id($primary_key, $data, $insert = false) {
        if ($primary_key && !$primary_key = intval($primary_key)) return false;
        // If we are allowed to insert data, first check if row with this id exists, and insert if needed
        if ($primary_key) {
            $exist_row = sql_fetsel($this->primary_key, $this->main_table, $this->main_table.'.'.$this->primary_key.' = '.$primary_key);
            if (!$exist_row and $insert) {
                $data[$this->primary_key] = $primary_key;
                return $this->insert($data);    
            } else {
                return $this->update($this->main_table.'.'.$this->primary_key.' = '.$primary_key, $data);
            }
        } else if ($insert) {            
            $data[$this->primary_key] = $primary_key;
            return $this->insert($data);
        } else return false;
    }
    
    /**
     *  Delete data rows
     * 
     *  @param Array (or string) $where Where conditions. Could be array of conditions, or simple string condition. Example: "prix>100"
     * 	 
	 *	@return bool True if OK and False otherwise
     */
    function delete($where = array()) {
        $tables = $this->main_table;        
        
        $tables_joins = '';
        if (!empty($this->joins)) 
            $tables_joins .= ' USING '.$this->main_table;
        // Join tables:
        foreach ($this->joins as $table_name=>$join) {
            if (isset($join['hard_join']) && $join['hard_join']!=TRUE) continue;
            $tables .= ', '.$table_name;
            $tables_joins .= ' '.$join['join_type'].
                      ' JOIN '.$table_name.' ON '.$this->main_table.'.'.$join['main_id'].
                      ' = '.$table_name.'.'.$join['join_id'];               
        }       
        $tables .= $tables_joins;
              
       return sql_delete($tables, $where);
    }
    
    /**
	 *  Delete row by ID (primary key)
     * 
     *  @param int $primary_key Primary key of row to delete
     *  @param array $data Array of row data
	 *
     *  @return bool True if OK and False otherwise
     */
    function delete_by_id($primary_key) {
        if ($primary_key) {
            return $this->delete($this->main_table.'.'.$this->primary_key.' = '.$primary_key);
        } else return false;
    }
    
    /**
     *  Create an empty row array.
     *  Note: This function creates empty elements for all fields with their real names and 
     *  also with aliases (if they are different from real names)
     * 
     *  @return array Empty associative array of data row 
     */
    function get_empty() {
        $row_data = array();
        
        // Main fields:        
        foreach ($this->main_fields as $real_name=>$alias) {
            $row_data[$real_name] = '';
            if ($alias != $real_name)
                $row_data[$alias] = '';                            
        }
        
        // Join fields:
        foreach ($this->joins as $table_name=>$join) {
            if (isset($join['fields']) and !empty($join['fields']))
            foreach ($join['fields'] as $real_name=>$alias) {
                $row_data[$real_name] = '';
                if ($alias != $real_name)
                    $row_data[$alias] = '';
            }
        }        
        return $row_data;
    }
    
    /**
     *  Validate data according to model validation rules
     *    
     *  @param array $data Data array for validation
     *  @param array $errors Validation errors array. Define this variable to get errors. Format: field_name=>Error message
     *  @param bool $add_message_erreur If true, message_erreur variable will be added to $errors array
     *  @return bool True if validation OK and False otherwise
     *  @author Aleksey Yaroshchuk (aleksey.yaroshchuk@gmail.com)
     */
    function validate($data, &$errors = NULL, $add_message_erreur = true) {
        $this->validation->set_data($data);
        $result = $this->validation->run();        
        $errors = $this->validation->_error_array;
        if (!$result and $add_message_erreur) 
            $errors['message_erreur'] = _T('pdk:validation_message_error'); 
        return $result;
    }
	
	/**
	*  Выборка элементов мультиязычного справочника в виде массива. 
	*  В процессе разработки функция была модифицирована, и теперь ее можно использовать не только 
	*  для простого справочника, а и для любой таблицы, чтобы выбрать простой ассоциативный массив типа ID=>TITRE
	*  Это обертка функции select_reference_to_array()
	*  	
	*  @param string $titre Имя поля, являющегося текстовым полем справочника (по умолчанию titre)
	*  @param string $lang Язык, для которого выбирать значение поля $titre. По умолчанию берется системное значение.
	*  @param string $where Where condition. Can be string or array - see sql_select()
	*  @param string $group_by Group by expression
	*  @param string $order_by Order by expression
	*  @param bool $supprimer_numero Логическое значение, указывающее нужно ли обрезать номер в начале текстового поля
	* 
	*  @return Array Массив справочника. Индексами массива являются первичные ключи, а элементами - значения поля $titre, выбранные для заданного языка
	*/
	function SelectReferenceToArray($titre = 'titre', $lang = null, $where = array(), $group_by = array(), $order_by = array(), $supprimer_numero = false) {
		include_spip('functions/pdk_database');		
		return select_reference_to_array($this->main_table, $this->primary_key, $titre, $lang, $where, $group_by, $order_by, $supprimer_numero);
	}
}
}
?>